﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AAPL.BAL.Persistence;
using AAPL.BAL.Persistence.Persisters;
using AAPL.BAL.Rules.Validators;
using AAPL.Common.DataShapes;
using AAPL.Common;
using System.Data;
using System.Data.SqlClient;

namespace AAPL.BAL.Services
{
    public partial class BlogService : ServiceBase
    {

        #region Properties

            public AAPL.BAL.Persistence.Persisters.BlogPostPersister BlogPostPersister { get; set; }

        #endregion


        #region Constructors

            public BlogService()
            {
                BlogPostPersister = new AAPL.BAL.Persistence.Persisters.BlogPostPersister();
            }

            public BlogService(AAPL.BAL.Persistence.Persisters.BlogPostPersister blogPostPersister)
            {
                BlogPostPersister = blogPostPersister;
            }

        #endregion



        //**************************************************************************************
        // BlogProfile Methods
        //**************************************************************************************
        

        public List<BlogProfile> GetListOfAllBlogProfiles(BlogProfileSortOption sortOption)
        {
            string query = @"SELECT * 
                            FROM BlogProfile 
                            " + GetBlogProfileOrderBy(sortOption);

            SqlDao dao = SharedSqlDao;
            SqlCommand command = dao.GetSqlCommand(query);
            return dao.GetList<BlogProfile>(command);
        }



        //**************************************************************************************
        // BlogPost Methods
        //**************************************************************************************


        public void Save(BlogPost post)
        {
            BlogPostValidator validator = new BlogPostValidator();
            List<RuleError> errors = validator.Validate(post);
            if (errors.Count == 0)
            {
                BlogPostPersister.Save(post);            
            }
        }

        
        public BlogPost GetMostRecentBlogPost()
        {
            string query = @"SELECT TOP 1 p.*, s.Score 
                            FROM [dbo].[BlogPost] p
                            LEFT JOIN [dbo].[BlogPostReputationScore] s on s.PostGuid = p.PostGuid
                            " + GetBlogPostOrderBy(BlogPostSortOption.ByDate);
            SqlDao dao = new SqlDao();
            SqlCommand command = dao.GetSqlCommand(query);
            return dao.GetSingle<BlogPost>(command);
        }


        public BlogPost GetBlogPost(Guid postGuid)
        {
            string query = @"SELECT p.*, s.Score 
                            FROM [dbo].[BlogPost] p
                            LEFT JOIN [dbo].[BlogPostReputationScore] s on s.PostGuid = p.PostGuid
                            WHERE PostGuid = @PostGuid";
            SqlDao dao = new SqlDao();
            SqlCommand command = dao.GetSqlCommand(query);
            command.Parameters.Add(dao.CreateParameter("@PostGuid", postGuid));
            return dao.GetSingle<BlogPost>(command);
        }


        public List<BlogPost> GetListOfBlogPostsForWeek(DateTime startDate, BlogPostSortOption sortOption)
        {
            startDate = ToSunday(startDate);
            DateTime startUtc = startDate.Date;
            DateTime endUtc = startDate.AddDays(7).Date;

            string query = @"SELECT p.*,  CASE WHEN s.Score IS NULL THEN 0 ELSE s.Score END AS Score 
                            FROM [dbo].[BlogPost] p
                            LEFT JOIN [dbo].[BlogPostReputationScore] s on s.PostGuid = p.PostGuid
                            WHERE PostedUtc > @StartUtc AND PostedUtc < @EndUtc 
                            " + GetBlogPostOrderBy(sortOption);
            
            SqlDao dao = new SqlDao();
            SqlCommand command = dao.GetSqlCommand(query); 
            command.Parameters.Add(dao.CreateParameter("@StartUtc", startUtc));
            command.Parameters.Add(dao.CreateParameter("@EndUtc",endUtc));
            return dao.GetList<BlogPost>(command);
        }


        // GetPageOfBlogPostSearchResultsForWeek is pretty complex for an example.  I wanted 
        // to include it to demonstrate how this framework is flexible enough to handle complex
        // queries, composite data shapes (BlogPostSearchResult contains data from entities other 
        // than BlogPost), and hierarchical data shapes (BlogPostSearchResuls contains a List<Category>). 
        // Simpler methods like GetMostRecentBlogPost() are easier to understand, but I find that
        // my real world data access methods tend to look more like this one.
        public DataPage<BlogPostSearchResult> GetPageOfBlogPostSearchResultsForWeek(DateTime startDate, int pageSize, int pageIndex, BlogPostSortOption sortOption)
        {
            // Populate the main member data.  We're not populating any
            // lists or hierarchical data at this point.
            startDate = ToSunday(startDate);
            DateTime startUtc = startDate.Date;
            DateTime endUtc = startDate.AddDays(7).Date;
            string query = string.Format(@"
                    WITH q1 AS
                    (
                        SELECT ROW_NUMBER() OVER({0}) AS RowNumber, p.*, b.BlogName, b.BlogUrl, b.BlogId, b.Image, CASE WHEN s.Score IS NULL THEN 0 ELSE s.Score END AS Score 
                        FROM [dbo].[BlogPost] p
                        JOIN [dbo].[BlogProfile] b ON p.BlogGuid = b.BlogGuid 
                        LEFT JOIN [dbo].[BlogPostReputationScore] s on s.PostGuid = p.PostGuid
                        WHERE (p.PostedUtc > @StartUtc AND p.PostedUtc < @EndUtc)  
                    )
                    SELECT q1.*, (SELECT COUNT(*) FROM q1) AS RecordCount
                    FROM q1
                    WHERE RowNumber > @PageSize * (@PageIndex)
                    AND RowNumber <= @PageSize * (@PageIndex + 1)
                    {0} ", GetBlogPostOrderBy(sortOption));

            SqlDao dao = SharedSqlDao;
            SqlCommand command = dao.GetSqlCommand(query);
            command.Parameters.Add(dao.CreateParameter("@StartUtc", startUtc));
            command.Parameters.Add(dao.CreateParameter("@EndUtc", endUtc));
            command.Parameters.Add(dao.CreateParameter("@PageSize", pageSize));
            command.Parameters.Add(dao.CreateParameter("@PageIndex", pageIndex));
            DataPage<BlogPostSearchResult> page = dao.GetDataPage<BlogPostSearchResult>(command, pageIndex, pageSize);
            // Now that we have a list of our main dtos, we can use
            // the ids from that list to pull our hierarchical data.
            CategoryService categoryService = new CategoryService();
            categoryService.PopulatePostCategories(page.Data);
            return page;
        }


        public DataPage<BlogPostSearchResult> GetPageOfBlogPostSearchResultsForCategory(Guid categoryGuid, int pageSize, int pageIndex, BlogPostSortOption sortOption)
        {
            // Populate the main member data.  We're not populating any
            // lists or hierarchical data at this point.
            string query = string.Format(@"
                    WITH q1 AS
                    (
                        SELECT ROW_NUMBER() OVER({0}) AS RowNumber, p.*, b.BlogName, b.BlogUrl, b.BlogId, b.Image, CASE WHEN s.Score IS NULL THEN 0 ELSE s.Score END AS Score 
                        FROM [dbo].[BlogPost] p
                        JOIN [dbo].[PostCategory] c on p.PostGuid = c.PostGuid 
                        JOIN BlogProfile b ON p.BlogGuid = b.BlogGuid 
                        LEFT JOIN [dbo].[BlogPostReputationScore] s on s.PostGuid = p.PostGuid
                        WHERE c.CategoryGuid = @CategoryGuid
                    )
                    SELECT q1.*, (SELECT COUNT(*) FROM q1) AS RecordCount
                    FROM q1
                    WHERE RowNumber > @PageSize * (@PageIndex)
                    AND RowNumber <= @PageSize * (@PageIndex + 1)
                    {0} ", GetBlogPostOrderBy(sortOption));

            SqlDao dao = SharedSqlDao;
            SqlCommand command = dao.GetSqlCommand(query);
            command.Parameters.Add(dao.CreateParameter("@PageSize", pageSize));
            command.Parameters.Add(dao.CreateParameter("@PageIndex", pageIndex));
            command.Parameters.Add(dao.CreateParameter("@CategoryGuid", categoryGuid));
            DataPage<BlogPostSearchResult> page = dao.GetDataPage<BlogPostSearchResult>(command, pageIndex, pageSize);
            // Now that we have a list of our main dtos, we can use
            // the ids from that list to pull our hierarchical data.
            CategoryService categoryService = new CategoryService();
            categoryService.PopulatePostCategories(page.Data);
            return page;
        }



        //**************************************************************************************
        // Enums
        //************************************************************************************** 
        
        
        public enum BlogPostSortOption
        {
            ByDate,
            ByBlogName,
            ByVote
        }

        
        public enum BlogProfileSortOption
        {
            ByBlogName,
            ByAuthorName,
            ByBlogId,
            ByVote
        }


        //**************************************************************************************
        // Utility Methods
        //************************************************************************************** 


        private string GetBlogPostOrderBy(BlogPostSortOption sortOption)
        {
            switch (sortOption)
            {
                case BlogPostSortOption.ByDate:
                    return "ORDER BY PostedUtc DESC";
                case BlogPostSortOption.ByBlogName:
                    return "ORDER BY BlogName ASC";
                case BlogPostSortOption.ByVote:
                    return "ORDER BY Score DESC";
                default:
                    return "ORDER BY PostedUtc DESC";
            }
        }


        private string GetBlogProfileOrderBy(BlogProfileSortOption sortOption)
        {
            switch (sortOption)
            {
                case BlogProfileSortOption.ByAuthorName:
                    return "ORDER BY AuthorName DESC";
                case BlogProfileSortOption.ByBlogName:
                    return "ORDER BY BlogName ASC";
                case BlogProfileSortOption.ByBlogId:
                    return "ORDER BY BlogId DESC";
                case BlogProfileSortOption.ByVote:
                    return "ORDER BY Score DESC";
                default:
                    return "ORDER BY BlogId DESC";
            }
        }


        public static DateTime ToSunday(DateTime date)
        {
            double offset = date.DayOfWeek == DayOfWeek.Sunday ? 0 : Convert.ToDouble(date.DayOfWeek);
            return date.AddDays(-offset);
        }

    }
}
